<?php


namespace commands;


use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

class EmsClientFee extends BaseCommand
{

    protected static $defaultName = 'zdf';
    const BCJG = '国际业务分公司';

    public function configure()
    {
        parent::configure();
        $this->setDescription('EMS终端费,压缩包文件：*_CKZDF.zip, *_CKYGF.zip, *_CKJZ.zip');
    }

    public function execute(InputInterface $input, OutputInterface $output)
    {
        parent::execute($input, $output);
        $dir = $this->getDataFilePath($input->getArgument('dir'));
        $output->writeln('国际终端费');
        $this->unzip(['*_CKZDF.zip', '*_CKYGF.zip', '*_CKJZ.zip'], $dir, $output);
        $file = $this->findFile($dir . '/temp/', 'zdf_0-999999999.csv', '==4', 'ems');
        //ckzdf-ems
        $this->doCsv($file->getPathname(), $output, function ($row, $n) {
            if ($n == 0) return;
            $city = $this->getCellFromRow($row, 'L');
            $this->processField('gjzdems', $city, $row, $n);
            $this->processField('ckygems', $city, $row, $n);
            $this->processField('ckclems', $city, $row, $n);
            $this->processRow('sf_ckclems', $row, $n);
            $this->processRow('sf_ckygems', $row, $n);
        });
        $output->writeln('出口处理费');
        //ckzdf-pac
        $file = $this->findFile($dir . '/temp/', 'zdf_0-999999999.csv', '==4', 'pac');
        //var_export($file);
        $this->doCsv($file->getPathname(), $output, function ($row, $n) {
            if ($n == 0) return;
            $city = $this->getCellFromRow($row, 'N');
            $this->processField('gjzdbg', $city, $row, $n);
            $this->processField('ckclbg', $city, $row, $n);
            $this->processField('ckjzbg', $city, $row, $n);
            $this->processField('ckygbg', $city, $row, $n);
            $this->processRow('sf_ckygbg', $row, $n);
            $this->processRow('sf_ckclbg', $row, $n);

        });
        $output->writeln('出口经转费1');
        $file = $this->findFile($dir . '/temp/', 'sd_ot1_trans.sql0-99999999.csv', '>=1');
        $this->doCsv($file->getPathname(), $output, function ($row, $n) {
            if ($n == 0) return;
            $city = $this->getCellFromRow($row, 'I');
            $this->processField('ckjzems', $city, $row, $n);
            $this->processField('ckjzbg', $city, $row, $n);
        });
        $output->writeln('出口经转费2');
        $file = $this->findFile($dir . '/temp/', 'sd_trans_2.sql0-99999999.csv', '>=1');
        $this->doCsv($file->getPathname(), $output, function ($row, $n) {
            if ($n == 0) return;
            $city = $this->getCellFromRow($row, 'I');
            $this->processRow('sf_ckclysyz', $row, $n);
        });
        $output->writeln('出口验关');
        $file = $this->findFile($dir . '/temp/', 'sd_customs_2.sql0-99999999.csv', '>=1');
        $this->doCsv($file->getPathname(), $output, function ($row, $n) {
            if ($n == 0) return;
            $city = $this->getCellFromRow($row, 'I');
            $this->processRow('sf_ckygysyz', $row, $n);
        });
        $dest = $dir . '/zdf_qfb.xlsx';
        $this->export($dest, './tpl/emszdf_qfb.xlsx', function (Spreadsheet $spreadsheet) {
            $sheet = $spreadsheet->getActiveSheet();
            $first_row = $this->findFirstRowToRender($sheet);
            @$this->processExport($this->data, $first_row, $sheet);
        });
        $this->export($dir . '/zdf_qfb2.xlsx', './tpl/emszdf_qfb.xlsx', function (Spreadsheet $spreadsheet) {
            $sheet = $spreadsheet->getActiveSheet();
            $first_row = $this->findFirstRowToRender($sheet);

            @$this->processExport($this->data2, $first_row, $sheet);
        });
        $this->export($dir . '/zdf_sfb.xlsx', './tpl/emszdf_sfb.xlsx', function (Spreadsheet $spreadsheet) {
            $sheet = $spreadsheet->getActiveSheet();
            $first_row = $this->findFirstRowToRender($sheet);
            $row = $first_row;
            foreach ($this->data as $city => $line) {
                if (!is_array($line)) continue;
                $sheet->setCellValue('A' . $row, $city);
                if (self::BCJG == $city) {
                    $sheet->setCellValueExplicit('B' . $row, round($this->data['sf_ckclysyz'], 2), DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicit('C' . $row, round($this->data['sf_ckclems'], 2), DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicit('D' . $row, round($this->data['sf_ckclbg'], 2), DataType::TYPE_NUMERIC);
                    $sheet->setCellValue('E' . $row, sprintf('=SUM(B%d:D%d)', $row, $row));
                    $sheet->setCellValueExplicit('F' . $row, round($this->data['sf_ckygysyz'], 2), DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicit('G' . $row, round($this->data['sf_ckygems'], 2), DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicit('H' . $row, round($this->data['sf_ckygbg'], 2), DataType::TYPE_NUMERIC);
                    $sheet->setCellValue('I' . $row, sprintf('=SUM(F%d:H%d)', $row, $row));
                }
                $sheet->setCellValue('J' . $row, sprintf('=SUM(E%d+I%d)', $row, $row));
                $row++;
            }
            $this->setSheetSubtotal($sheet, $first_row, $row);

        });
        $this->rmTemp($dir, $output);
    }

    /**
     * W
     * @param $row
     * @param $n
     * @return string
     * @throws \Exception
     */
    public function gjzdems($row, $n)
    {
        return $this->getCellFromRow($row, 'T');
    }

    /**
     * X
     * @param $row
     * @param $n
     * @return string
     * @throws \Exception
     */
    public function gjzdbg($row, $n)
    {
        return $this->getCellFromRow($row, 'U');
    }

    /**
     * Y
     * @param $row
     * @param $n
     */
    public function ckclems($row, $n)
    {
        $q = $this->getCellFromRow($row, 'q');
        if ($q <= 3) {
            return 0.3;
        }
        return ceil($q - 3) * 0.15 + 0.3;
    }

    /**
     * Z
     * @param $row
     * @param $n
     * @return float
     * @throws \Exception
     */
    public function ckclbg($row, $n)
    {
        $r = $this->getCellFromRow($row, 'R');
        if ($r <= 3) {
            return 0.3;
        }
        return ceil($r - 3) * 0.15 + 0.3;
    }

    /**
     * T
     * @param $row
     * @return int|string
     * @throws \Exception
     */
    public function ckjzems($row)
    {
        return iconvStr($this->getCellFromRow($row, 'A')) == '特快专递' ? $this->getCellFromRow($row, 'N') : 0;
    }

    /**
     * U
     * @param $row
     * @return int|string
     * @throws \Exception
     */
    public function ckjzbg($row)
    {
        return iconvStr($this->getCellFromRow($row, 'A')) == '包裹' ? $this->getCellFromRow($row, 'N') : 0;
    }

    public function ckygems($row)
    {
        return 2;
    }

    public function ckygbg($row)
    {
        return 2;
    }

    public function sf_ckclysyz($row, $n)
    {
        return $this->getCellFromRow($row, 'N');
    }

    public function sf_ckclems($row, $n)
    {
        return $this->ckclems($row, $n);
    }

    public function sf_ckclbg($row, $n)
    {
        return $this->ckclbg($row, $n);
    }

    public function sf_ckygysyz($row, $n)
    {
        return $this->getCellFromRow($row, 'N');
    }

    public function sf_ckygems($row, $n)
    {
        return 2;
    }

    public function sf_ckygbg($row, $n)
    {
        return 2;
    }

    public function processExport($array, $first_row, Worksheet $sheet)
    {
        $row = $first_row;
        foreach ($array as $city => $line) {
            if (!is_array($line)) continue;
            $sheet->setCellValue('A' . $row, $city);
            $sheet->setCellValueExplicit('B' . $row, round($line['gjzdems'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('C' . $row, round($line['gjzdbg'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValue('D' . $row, sprintf('=SUM(B%d:C%d)', $row, $row));
            $sheet->setCellValueExplicit('E' . $row, round($line['ckclems'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('F' . $row, round($line['ckclbg'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValue('G' . $row, sprintf('=SUM(E%d:F%d)', $row, $row));
            $sheet->setCellValueExplicit('H' . $row, round($line['ckjzems'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('I' . $row, round($line['ckjzbg'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValue('J' . $row, sprintf('=SUM(H%d:I%d)', $row, $row));
            $sheet->setCellValueExplicit('K' . $row, round($line['ckygems'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValueExplicit('L' . $row, round($line['ckygbg'], 2), DataType::TYPE_NUMERIC);
            $sheet->setCellValue('M' . $row, sprintf('=SUM(K%d:L%d)', $row, $row));
            $sheet->setCellValue('N' . $row, sprintf('=SUM(M%d+J%d+G%d+D%d)', $row, $row, $row, $row));
            $row++;
        }
        $this->setSheetSubtotal($sheet, $first_row, $row, 'N');
    }
}
